CREATE TABLE dm_terminal_sign_punctuality_rate_dt (
  date_time date NULL COMMENT '日期',
  final_sign_user_code varchar(100) NULL COMMENT '最后签收派件员编码',
  final_sign_network_code varchar(100) NULL COMMENT '最终签收派件网点编码',
  end_franchisee_code varchar(100) NULL COMMENT '末端网点所属加盟商编码',
  end_agent_code varchar(100) NULL COMMENT '末端网点所属代理区编码',
  end_agent_name varchar(100) NULL COMMENT '末端网点所属代理区名称',
  end_provider_id varchar(100) NULL COMMENT '末端网点所属省份id',
  end_provider_name varchar(100) NULL COMMENT '末端网点所属省份名称',
  end_city_id varchar(100) NULL COMMENT '末端网点所属城市id',
  end_city_name varchar(100) NULL COMMENT '末端网点所属城市名称',
  end_area_id varchar(100) NULL COMMENT '末端网点所属区县id',
  end_area_name varchar(100) NULL COMMENT '末端网点所属区县名称',
  end_franchisee_name varchar(100) NULL COMMENT '末端网点所属加盟商名称',
  final_sign_network_name varchar(100) NULL COMMENT '最终签收派件网点名称',
  final_sign_user_name varchar(100) NULL COMMENT '最后签收派件员名称',
  need_sign_count bigint(20) NULL COMMENT '应签收汇总',
  all_sign_22 bigint(20) NULL COMMENT '22点前全量签收量',
  all_sign_24 bigint(20) NULL COMMENT '24点前全量签收量',
  need_sign_cnt_1030 bigint(20) NULL COMMENT '10:30应签收量',
  aging_sign_cnt_1030 bigint(20) NULL COMMENT '10:30准点签收量',
  aging_sign_over_time_1030_22 bigint(20) NULL COMMENT '10:30 22点前延误签收量',
  aging_sign_over_time_1030_24 bigint(20) NULL COMMENT '10:30 2224点前延误签收量',
  aging_nosign_cnt_1030 bigint(20) NULL COMMENT '10:30未签收量',
  need_sign_cnt_1130 bigint(20) NULL COMMENT '11:30应签收量',
  aging_sign_cnt_1130 bigint(20) NULL COMMENT '11:30准点签收量',
  aging_sign_over_time_1130_22 bigint(20) NULL COMMENT '11:30 22点前延误签收量',
  aging_sign_over_time_1130_24 bigint(20) NULL COMMENT '11:30 22-24点延误签收量',
  aging_nosign_cnt_1130 bigint(20) NULL COMMENT '11:30未签收量',
  need_sign_cnt_1400 bigint(20) NULL COMMENT '14:00应签收量',
  aging_sign_cnt_1400 bigint(20) NULL COMMENT '14:00准点签收量',
  aging_sign_over_time_1400_22 bigint(20) NULL COMMENT '14:00 22点前延误签收量',
  aging_sign_over_time_1400_24 bigint(20) NULL COMMENT '14:00 22-24点前延误签收量',
  aging_nosign_cnt_1400 bigint(20) NULL COMMENT '14:00未签收量',
  need_sign_cnt_1800 bigint(20) NULL COMMENT '18:00应签收量',
  aging_sign_cnt_1800 bigint(20) NULL COMMENT '18:00准点签收量',
  aging_sign_over_time_1800_22 bigint(20) NULL COMMENT '18:00 22点前延误签收量',
  aging_sign_over_time_1800_24 bigint(20) NULL COMMENT '18:00 22-24点延误签收量',
  aging_nosign_cnt_1800 bigint(20) NULL COMMENT '18:00未签收量',
  need_sign_cnt_2200 bigint(20) NULL COMMENT '22:00应签收量',
  aging_sign_cnt_2200 bigint(20) NULL COMMENT '22:00准点签收量',
  aging_sign_over_time_2200_22 bigint(20) NULL COMMENT '22:00 22点前延误签收量',
  aging_sign_over_time_2200_24 bigint(20) NULL COMMENT '22:00 22-24点前延误签收量',
  aging_nosign_cnt_2200 bigint(20) NULL COMMENT '22:00未签收量',
  order_source_name varchar(100) comment'订单来源',
  order_source_code varchar(100)  comment '订单来源code',
   end_brand_code varchar(100) comment '共配code',
  end_brand_name varchar(100) comment '共配名字'
     ,is_shaidan  int(6) comment '是否筛单网点'
     ,is_delay   int(6) comment '是否顺延网点'
     ,is_new_open  int(6) comment '是否新开网点'
     ,is_special_report  int(6) comment '是否特殊上报网点'
     ,yizhan_sum int(6) comment'驿站'
     ,guiji_sum int(6) comment'柜机'
     ,shsm_sum int(6) comment'送货上门'
     ,township_sum int(6) comment'乡镇件'
     ,shift_1_need_count int(6) comment'一班应签收量'
     ,shift_1_sign int(6) comment'一班签收量'
     ,shift_2_need_count int(6) comment'二班应签收量'
     ,shift_2_sign int(6) comment' 二班签收量'
     ,earliest_deliver_time varchar(100) comment'最早出仓时间'
     ,last_deliver_time     varchar(100) comment'最晚出仓时间'
    ,actual_sign_22_count  int(8) comment '22点签收量'
    ,actual_sign_00_count int(8) comment '24点前前签收量'
) ENGINE=OLAP
DUPLICATE KEY(date_time, final_sign_user_code, final_sign_network_code, end_franchisee_code)
COMMENT '时效签收准点签收率报表新'
PARTITION BY RANGE(date_time)
(START ('2022-09-25') END ('2022-11-15') EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(final_sign_user_code) BUCKETS 4
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-365',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '4',
'in_memory' = 'false',
'storage_format' = 'V2'
);

alter table jms_dm.dm_terminal_sign_punctuality_rate_dt add column (
      no_township_sum int(8) comment '非乡镇件'  --
     , township_sign_sum int(8) comment '乡镇件22点前签收量'  --
     , no_township_sign_sum int(8) comment '非乡镇件22点前签收量'  --
    )  ;